package com.boco.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.boco.entity.EMR_XYCF;

public class GetEMR_XYCFData {
	
	private static final Log log = LogFactory.getLog(GetEMR_XYCFData.class);
	public static List<EMR_XYCF> getData()
	{
	    Connection con = null;
	    PreparedStatement pre = null;
	    ResultSet result = null;
	    List<EMR_XYCF> re = new ArrayList<EMR_XYCF>();
	    try
	    {
	        Class.forName("oracle.jdbc.driver.OracleDriver");
	        System.out.println("开始尝试连接数据库！");
	        String url = "jdbc:oracle:" + "thin:@172.20.21.12:1521:orcl";
	        String user = "sjzx_hxk";
	        String password = "boco#1234";
	        con = DriverManager.getConnection(url, user, password);
	        System.out.println("连接成功！");
	        String sql = "select "
		        	+"'' as HZZSYH,"
		        	+"a.yljgdm as YLJGZZJGDM,"
					+"'' as  YLJGMC,"
					+"a.jzlsh as MJZH,"
					+"a.cfh as CFBH,"
					+"to_char(a.kfrq,'yyyy-MM-dd HH24:mi:ss') as  KCFRQ,"
					+"a.cfyxts as CFYXTS,"
					+"a.jzksdm as KCFKSMCDM,"
					+"a.jzksmc as KCFKSMC,"
					+"a.hzxm as XM,"
					+"case a.hzxb when '男' then 1 else 2 end as XB,"
					+"a.hznl as NL,"
					+"a.kfysgh as KCFYSGH,"
					+"a.kfysxm as KCFYSQM,"
					+"a.cfje as CFYPJE,"
					+"a.cfshyjsbh as CFSHYJSGH,"
					+"a.cfshyjsqm as CFSHYJSXM,"
					+"a.cfpyjsbh as CFDPYJSGH,"
					+"a.cfshyjsqm as CFDPYJSQM,"
					+"a.cfpyjsbh as FYYJSGH,"
					+"a.cfpyjsqm as FYYJSQM,"
					+"a.bz as CFBZXX,"
					+"'' as  XYZDBM,"
					+"'' as XYZDMC,"
					+"a.jzlsh as ZRLSH,"
					+"to_char(a.ywscsj,'yyyy-MM-dd HH24:mi:ss') as BCRQ,"
					+"'' as ORGCODE,"
					+"'' as AREACODE,"
					+"to_char(sysdate,'yyyyMMddHH24miss') as UPLOADTIME,"
					+"'' as  SOURCEID "
					+" from tb_mz_cfzb a ";
	 
	        log.info("查询sql："+sql);
	        pre = con.prepareStatement(sql);
	        result = pre.executeQuery();
	        System.out.println("执行成功");
	        while (result.next()){
//	            System.out.println(result.getInt("YYDAH"));
	        	EMR_XYCF temp = new EMR_XYCF();
	        	temp.setHZZSYH(result.getString("HZZSYH"));
	        	temp.setYLJGZZJGDM(result.getString("YLJGZZJGDM"));
	        	temp.setYLJGMC(result.getString("YLJGMC"));
	        	temp.setMJZH(result.getString("MJZH"));
	        	temp.setCFBH(result.getString("CFBH"));
	        	temp.setKCFRQ(result.getString("KCFRQ"));
	        	temp.setCFYXTS(result.getString("CFYXTS"));
	        	temp.setKCFKSMCDM(result.getString("KCFKSMCDM"));
	        	temp.setKCFKSMC(result.getString("KCFKSMC"));
	        	temp.setXM(result.getString("XM"));
	        	temp.setXB(result.getString("XB"));
	        	temp.setNL(result.getString("NL"));
	        	temp.setKCFYSGH(result.getString("KCFYSGH"));
	        	temp.setKCFYSQM(result.getString("KCFYSQM"));
	        	temp.setCFYPJE(result.getString("CFYPJE"));
	        	temp.setCFSHYJSGH(result.getString("CFSHYJSGH"));
	        	temp.setCFSHYJSXM(result.getString("CFSHYJSXM"));
	        	temp.setCFDPYJSGH(result.getString("CFDPYJSGH"));
	        	temp.setCFDPYJSQM(result.getString("CFDPYJSQM"));
	        	temp.setFYYJSGH(result.getString("FYYJSGH"));
	        	temp.setFYYJSQM(result.getString("FYYJSQM"));
	        	temp.setCFBZXX(result.getString("CFBZXX"));
	        	temp.setXYZDBM(result.getString("XYZDBM"));
	        	temp.setXYZDMC(result.getString("XYZDMC"));
	        	temp.setZRLSH(result.getString("ZRLSH"));
	        	temp.setBCRQ(result.getString("BCRQ"));
	        	temp.setORGCODE(result.getString("ORGCODE"));
	        	temp.setAREACODE(result.getString("AREACODE"));
	        	temp.setUPLOADTIME(result.getString("UPLOADTIME"));
	        	temp.setSOURCEID(result.getString("SOURCEID"));
	        	re.add(temp);
	        }
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }
	    finally
	    {
	        try
	        {
	            if (result != null)
	                result.close();
	            if (pre != null)
	                pre.close();
	            if (con != null)
	                con.close();
	            System.out.println("数据库连接已关闭！");
	        }
	        catch (Exception e)
	        {
	            e.printStackTrace();
	        }
	    }
		return re;
	}
}
